/* 
 * Copyright 2011 kOSMik.
 * This file is part of kOSMik.
 * 
 * kOSMik is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * kOSMik is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with kOSMik.  If not, see <http://www.gnu.org/licenses/>.
 *  
 * kOSMik:
 * Open Street Map - Editor.
 * http://code.google.com/p/swp-dv-ws2010-osm-2/
 */

package de.fuberlin.kosmik.data.osmt;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.Context;
import android.database.Cursor;

/**
 * Implementation of the {@link OsmtData}-interface.
 * 
 * @author Johannes
 * @see OsmtData
 * 
 */
class OsmtDataImpl implements OsmtData {

	private final OsmtDatabase database;
	private final String appLanguage;
	private static final String DEFAULT_LANGUAGE = "EN";

	/**
	 * Constructor.
	 * 
	 * @param context The context from which you want to access the database.
	 */
	public OsmtDataImpl(Context context) {
		this.appLanguage = context.getResources().getConfiguration().locale.getLanguage()
				.toUpperCase();
		this.database = new OsmtDatabaseImpl(context);
	}

	/**
	 * 
	 * @return the database the context connect with
	 */
	OsmtDatabase getDatabase() {
		return database;
	}

	/**
	 * @return Returns the language code for this {@link java.util.Locale} set
	 *         up in this app.
	 */
	String getAppLanguage() {
		return appLanguage;
	}

	/**
	 * @return Returns the language code for this default language. The default
	 *         language is set to EN
	 */
	static String getDefaultLanguage() {
		return DEFAULT_LANGUAGE;
	}

	private String implode(String delimiter, String... strings) {
		final int len = strings.length;
		if (len > 0) {
			StringBuffer result = new StringBuffer(strings[0]);
			for (int i = 1; i < len; i++) {
				result.append(delimiter).append(strings[i]);
			}
			return result.toString();
		}
		return "";
	}

	private String selectClause(String... strings) {
		return "SELECT " + implode(", ", strings);
	}

	@Override
	public void traverseAllFeatures(OnObtainCursorListener onObtainCursorListener) {
		// TODO not implemented yet.

	}

	@Override
	public Collection<OsmtFeatureRecord> fetchAllFeaturesThatMatches(String searchTerm,
			int filterElements) {

		final Collection<OsmtFeatureRecord> results = new ArrayList<OsmtFeatureRecord>();
		final String lowerCaseSearchTerm = searchTerm.toLowerCase();
		final String selectClause = selectClause(OsmtFeature.COLUMN_KEY_NAME,
				OsmtFeature.COLUMN_VALUE_NAME, OsmtFeature.COLUMN_POI, OsmtFeature.COLUMN_WAY,
				OsmtFeature.COLUMN_AREA, OsmtFeature.COLUMN_ICON, OsmtFeature.COLUMN_WIKI,
				OsmtFeature.COLUMN_RENDER, OsmtFeature.COLUMN_PHOTO, OsmtFeature.COLUMN_USE,
				OsmtFeatureLangText.COLUMN_COMMENT);
		final String fromClause = String.format(
				" FROM %s LEFT OUTER JOIN %s ON %s=%s AND %s=%s ", OsmtFeature.TABLE,
				OsmtFeatureLangText.TABLE, OsmtFeature.COLUMN_KEY_NAME,
				OsmtFeatureLangText.COLUMN_KEY_NAME, OsmtFeature.COLUMN_VALUE_NAME,
				OsmtFeatureLangText.COLUMN_VALUE_NAME);
		String whereClause = String
				.format(" WHERE %s = '%s' AND ( LOWER(%s) LIKE '%%%s%%' OR LOWER(%s) LIKE '%%%s%%' OR LOWER(%s) LIKE '%%%s%%') ",
						OsmtFeatureLangText.COLUMN_LANGUAGE, appLanguage,
						OsmtFeature.COLUMN_KEY_NAME, lowerCaseSearchTerm,
						OsmtFeature.COLUMN_VALUE_NAME, lowerCaseSearchTerm,
						OsmtFeatureLangText.COLUMN_COMMENT, lowerCaseSearchTerm);
		final boolean filterPois = OsmtElementRecord.isNode(filterElements);
		final boolean filterWays = OsmtElementRecord.isWay(filterElements);
		final boolean filterAreas = OsmtElementRecord.isArea(filterElements);

		if (filterPois) {
			whereClause += String.format(" AND %s = '%d' ", OsmtFeature.COLUMN_POI,
					Integer.valueOf(1));
		}
		if (filterWays) {
			whereClause += String.format(" AND %s = '%d' ", OsmtFeature.COLUMN_WAY,
					Integer.valueOf(1));
		}
		if (filterAreas) {
			whereClause += String.format(" AND %s = '%d' ", OsmtFeature.COLUMN_AREA,
					Integer.valueOf(1));
		}
		final String orderClause = String.format("ORDER BY %s DESC", OsmtFeature.COLUMN_USE);
		final String query = selectClause + fromClause + whereClause + orderClause;
		database.query(query, new OnObtainCursorListener() {

			@Override
			public boolean onObtainCursor(Cursor cursor) {
				OsmtFeatureRecord resultFeature;
				if (cursor.moveToFirst()) {

					final int columnKeyPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtFeature.COLUMN_KEY_NAME);
					final int columnValuePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtFeature.COLUMN_VALUE_NAME);
					final int columnPoiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_POI);
					final int columnWayPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_WAY);
					final int columnAreaPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_AREA);
					final int columnIconPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_ICON);
					final int columnWikiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_WIKI);
					final int columnRenderPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_RENDER);
					final int columnPhotoPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_PHOTO);
					final int columnUsePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_USE);
					final int columnCommentPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtFeatureLangText.COLUMN_COMMENT);

					do {
						resultFeature = new OsmtFeatureRecord();
						resultFeature.osmKey = cursor.getString(columnKeyPosition);
						resultFeature.osmValue = cursor.getString(columnValuePosition);
						resultFeature.elements = OsmtElementRecord.convertElementsToInt(
								cursor.getInt(columnPoiPosition) == 1,
								cursor.getInt(columnWayPosition) == 1,
								cursor.getInt(columnAreaPosition) == 1);
						resultFeature.icon = cursor.getString(columnIconPosition);
						resultFeature.wiki = cursor.getString(columnWikiPosition);
						resultFeature.rendering = cursor.getString(columnRenderPosition);
						resultFeature.photo = cursor.getString(columnPhotoPosition);
						resultFeature.use = cursor.getInt(columnUsePosition);
						resultFeature.comment = cursor.getString(columnCommentPosition);
						results.add(resultFeature);
					} while (cursor.moveToNext());
					return true;
				}
				return false;
			}
		});
		return results;
	}

	private Collection<OsmtNodeRecord> fetchAllNodesThatMatches(String searchTerm,
			int filterElements, boolean selectBundlesButNoThemes) {
		final Collection<OsmtNodeRecord> results = new ArrayList<OsmtNodeRecord>();

		final String lowerCaseSearchTerm = searchTerm.toLowerCase();
		final String selectClause = selectClause(OsmtNode.COLUMN_NODE_ID,
				OsmtNode.COLUMN_THEME_ID, OsmtNode.COLUMN_POI, OsmtNode.COLUMN_WAY,
				OsmtNode.COLUMN_AREA, OsmtNode.COLUMN_ICON, OsmtNode.COLUMN_WIKI,
				OsmtNode.COLUMN_USE, OsmtNode.COLUMN_IS_BUNDLE,
				OsmtNodeLangText.COLUMN_DISPLAY_NAME, OsmtNodeLangText.COLUMN_COMMENT);
		final String fromClause = String.format(
				" FROM %s LEFT OUTER JOIN %s ON %s=%s AND %s='%s' ", OsmtNode.TABLE,
				OsmtNodeLangText.TABLE, OsmtNode.COLUMN_NODE_ID,
				OsmtNodeLangText.COLUMN_NODE_ID, OsmtNodeLangText.COLUMN_LANGUAGE, appLanguage);
		String whereClause = String.format(
				" WHERE %s = '%d' AND ( LOWER(%s) LIKE '%%%s%%' OR LOWER(%s) LIKE '%%%s%%')  ",
				OsmtNode.COLUMN_IS_BUNDLE, Integer.valueOf(selectBundlesButNoThemes ? 1 : 0),
				OsmtNodeLangText.COLUMN_DISPLAY_NAME, lowerCaseSearchTerm,
				OsmtNodeLangText.COLUMN_COMMENT, lowerCaseSearchTerm);

		final boolean filterPois = OsmtElementRecord.isNode(filterElements);
		final boolean filterWays = OsmtElementRecord.isWay(filterElements);
		final boolean filterAreas = OsmtElementRecord.isArea(filterElements);

		if (filterPois) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_POI,
					Integer.valueOf(1));
		}
		if (filterWays) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_WAY,
					Integer.valueOf(1));
		}
		if (filterAreas) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_AREA,
					Integer.valueOf(1));
		}
		final String orderClause = String.format("ORDER BY %s DESC", OsmtNode.COLUMN_USE);
		final String query = selectClause + fromClause + whereClause + orderClause;
		database.query(query, new OnObtainCursorListener() {

			@Override
			public boolean onObtainCursor(Cursor cursor) {
				OsmtNodeRecord resultNode;
				if (cursor.moveToFirst()) {
					final int columnNodeIdPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_NODE_ID);
					final int columnThemeIdPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_THEME_ID);
					final int columnPoiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_POI);
					final int columnWayPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_WAY);
					final int columnAreaPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_AREA);
					final int columnIconPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_ICON);
					final int columnWikiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_WIKI);
					final int columnUsePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_USE);
					final int columnIsBundlePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_IS_BUNDLE);
					final int columnDisplayNamePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtNodeLangText.COLUMN_DISPLAY_NAME);
					final int columnCommentPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtNodeLangText.COLUMN_COMMENT);
					do {
						resultNode = new OsmtNodeRecord();
						resultNode.id = cursor.getInt(columnNodeIdPosition);
						resultNode.themeId = cursor.getInt(columnThemeIdPosition);
						resultNode.elements = OsmtElementRecord.convertElementsToInt(
								cursor.getInt(columnPoiPosition) == 1,
								cursor.getInt(columnWayPosition) == 1,
								cursor.getInt(columnAreaPosition) == 1);
						resultNode.icon = cursor.getString(columnIconPosition);
						resultNode.wiki = cursor.getString(columnWikiPosition);
						resultNode.use = cursor.getInt(columnUsePosition);
						resultNode.isBundle = cursor.getInt(columnIsBundlePosition) == 1;
						resultNode.displayName = cursor.getString(columnDisplayNamePosition);
						resultNode.comment = cursor.getString(columnCommentPosition);
						results.add(resultNode);
					} while (cursor.moveToNext());
					return true;
				}
				return false;
			}
		});
		return results;
	}

	@Override
	public Collection<OsmtNodeRecord> fetchAllBundlesThatMatches(String searchTerm,
			int filterElements) {
		return fetchAllNodesThatMatches(searchTerm, filterElements, true);
	}

	@Override
	public Collection<OsmtNodeRecord> fetchAllThemesThatMatches(String searchTerm,
			int filterElements) {
		return fetchAllNodesThatMatches(searchTerm, filterElements, false);
	}

	@Override
	public List<Integer> fetchAllIdsOfChildNodesRelatedToParentIdFilteredByElements(
			final int parentId, final int filterElements) {
		final List<Integer> results = new ArrayList<Integer>();

		final String selectClause = selectClause(OsmtNode.COLUMN_NODE_ID);
		final String fromClause = String.format(" FROM %s ", OsmtNode.TABLE);
		String whereClause = String.format(" WHERE %s = '%d'", OsmtNode.COLUMN_THEME_ID,
				Integer.valueOf(parentId));

		final boolean filterPois = OsmtElementRecord.isNode(filterElements);
		final boolean filterWays = OsmtElementRecord.isWay(filterElements);
		final boolean filterAreas = OsmtElementRecord.isArea(filterElements);

		if (filterPois) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_POI,
					Integer.valueOf(1));
		}
		if (filterWays) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_WAY,
					Integer.valueOf(1));
		}
		if (filterAreas) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_AREA,
					Integer.valueOf(1));
		}
		final String orderClause = String.format("ORDER BY %s DESC", OsmtNode.COLUMN_USE);
		final String query = selectClause + fromClause + whereClause + orderClause;
		database.query(query, new OnObtainCursorListener() {

			@Override
			public boolean onObtainCursor(Cursor cursor) {
				if (cursor.moveToFirst()) {
					final int columnNodeIdPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_NODE_ID);
					do {
						results.add(Integer.valueOf(cursor.getInt(columnNodeIdPosition)));
					} while (cursor.moveToNext());
					return true;
				}
				return false;
			}
		});
		return results;
	}

	@Override
	public List<OsmtFeatureRecord> fetchAllFeaturesOfBundleWithId(int bundleId) {
		final List<OsmtFeatureRecord> results = new ArrayList<OsmtFeatureRecord>();

		final String selectClause = selectClause(OsmtBundlesToFeatures.COLUMN_KEY_NAME,
				OsmtBundlesToFeatures.COLUMN_VALUE_NAME, OsmtFeature.COLUMN_POI,
				OsmtFeature.COLUMN_WAY, OsmtFeature.COLUMN_AREA, OsmtFeature.COLUMN_ICON,
				OsmtFeature.COLUMN_WIKI, OsmtFeature.COLUMN_RENDER, OsmtFeature.COLUMN_PHOTO,
				OsmtFeature.COLUMN_USE, OsmtBundlesToFeatures.COLUMN_MODIFIABLE);
		final String fromClause = String.format(
				" FROM %s LEFT OUTER JOIN %s ON %s = %s AND %s = %s ",
				OsmtBundlesToFeatures.TABLE, OsmtFeature.TABLE, OsmtFeature.COLUMN_KEY_NAME,
				OsmtBundlesToFeatures.COLUMN_KEY_NAME, OsmtFeature.COLUMN_VALUE_NAME,
				OsmtBundlesToFeatures.COLUMN_VALUE_NAME);
		String whereClause = String.format(" WHERE %s = '%d'",
				OsmtBundlesToFeatures.COLUMN_BUNDLE_ID, Integer.valueOf(bundleId));

		final String query = selectClause + fromClause + whereClause;
		database.query(query, new OnObtainCursorListener() {

			@Override
			public boolean onObtainCursor(Cursor cursor) {
				OsmtFeatureRecord resultFeature;
				if (cursor.moveToFirst()) {
					final int columnKeyPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtBundlesToFeatures.COLUMN_KEY_NAME);
					final int columnValuePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtBundlesToFeatures.COLUMN_VALUE_NAME);
					final int columnPoiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_POI);
					final int columnWayPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_WAY);
					final int columnAreaPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_AREA);
					final int columnIconPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_ICON);
					final int columnWikiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_WIKI);
					final int columnRenderPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_RENDER);
					final int columnPhotoPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_PHOTO);
					final int columnUsePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtFeature.COLUMN_USE);
					final int columnModifiablePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtBundlesToFeatures.COLUMN_MODIFIABLE);
					/*
					 * final int columnCommentPosition = OsmtDatabaseUtils
					 * .detectColumnIndexOfCursorByName(cursor,
					 * OsmtFeatureLangText.COLUMN_COMMENT);
					 */
					do {
						resultFeature = new OsmtFeatureRecord();
						resultFeature.osmKey = cursor.getString(columnKeyPosition);
						resultFeature.osmValue = cursor.getString(columnValuePosition);
						resultFeature.elements = OsmtElementRecord.convertElementsToInt(
								cursor.getInt(columnPoiPosition) == 1,
								cursor.getInt(columnWayPosition) == 1,
								cursor.getInt(columnAreaPosition) == 1);
						resultFeature.icon = cursor.getString(columnIconPosition);
						resultFeature.wiki = cursor.getString(columnWikiPosition);
						resultFeature.rendering = cursor.getString(columnRenderPosition);
						resultFeature.photo = cursor.getString(columnPhotoPosition);
						resultFeature.use = cursor.getInt(columnUsePosition);
						resultFeature.modifiable = cursor.getInt(columnModifiablePosition) != 0;
						// resultFeature.comment =
						// cursor.getString(columnCommentPosition);
						results.add(resultFeature);
					} while (cursor.moveToNext());
					return true;
				}
				return false;
			}
		});
		return results;
	}

	@Override
	public List<OsmtKeyRecord> fetchAllKeys() {
		final List<OsmtKeyRecord> results = new ArrayList<OsmtKeyRecord>();

		final String selectClause = selectClause(OsmtKey.COLUMN_OSM_NAME, OsmtKey.COLUMN_USE,
				OsmtKey.COLUMN_WIKI, OsmtKey.COLUMN_TYPE);
		final String fromClause = String.format(" FROM %s ", OsmtKey.TABLE);
		final String orderClause = String.format("ORDER BY %s DESC, %s ASC",
				OsmtKey.COLUMN_USE, OsmtKey.COLUMN_OSM_NAME);

		final String query = selectClause + fromClause + orderClause;
		database.query(query, new OnObtainCursorListener() {

			@Override
			public boolean onObtainCursor(Cursor cursor) {
				OsmtKeyRecord resultKey;
				if (cursor.moveToFirst()) {
					final int columnNamePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtKey.COLUMN_OSM_NAME);
					final int columnUsePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtKey.COLUMN_USE);
					final int columnWikiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtKey.COLUMN_WIKI);
					final int columnTypePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtKey.COLUMN_TYPE);

					do {
						resultKey = new OsmtKeyRecord();
						resultKey.osmName = cursor.getString(columnNamePosition);
						resultKey.use = cursor.getInt(columnUsePosition);
						resultKey.wiki = cursor.getString(columnWikiPosition);
						resultKey.type = OsmtKey.enumToType(cursor
								.getString(columnTypePosition));
						results.add(resultKey);
					} while (cursor.moveToNext());
					return true;
				}
				return false;
			}
		});
		return results;
	}

	@Override
	public List<OsmtValueRecord> fetchAllValuesForKey(String keyName) {
		final List<OsmtValueRecord> results = new ArrayList<OsmtValueRecord>();

		final String selectClause = selectClause(OsmtFeature.COLUMN_VALUE_NAME,
				OsmtValue.COLUMN_USE);
		final String fromClause = String.format(" FROM %s JOIN %s ON %s = %s",
				OsmtFeature.TABLE, OsmtValue.TABLE, OsmtFeature.COLUMN_VALUE_NAME,
				OsmtValue.COLUMN_OSM_NAME);
		String whereClause = String.format(" WHERE %s = '%s'", OsmtFeature.COLUMN_KEY_NAME,
				keyName);
		final String orderClause = String.format("ORDER BY %s DESC, %s ASC",
				OsmtValue.COLUMN_USE, OsmtValue.COLUMN_OSM_NAME);

		final String query = selectClause + fromClause + whereClause + orderClause;
		database.query(query, new OnObtainCursorListener() {

			@Override
			public boolean onObtainCursor(Cursor cursor) {
				OsmtValueRecord resultValue;
				if (cursor.moveToFirst()) {
					final int columnNamePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtFeature.COLUMN_VALUE_NAME);
					final int columnUsePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtValue.COLUMN_USE);

					do {
						resultValue = new OsmtValueRecord();
						resultValue.osmName = cursor.getString(columnNamePosition);
						resultValue.use = cursor.getInt(columnUsePosition);
						results.add(resultValue);
					} while (cursor.moveToNext());
					return true;
				}
				return false;
			}
		});
		return results;
	}

	@Override
	public Map<Integer, OsmtNodeRecord> fetchAllChildNodesRelatedToParentIdFilteredByElements(
			int parentId, int filterElements) {
		final Map<Integer, OsmtNodeRecord> results = new HashMap<Integer, OsmtNodeRecord>();

		final String selectClause = selectClause(OsmtNode.COLUMN_NODE_ID,
				OsmtNode.COLUMN_THEME_ID, OsmtNode.COLUMN_POI, OsmtNode.COLUMN_WAY,
				OsmtNode.COLUMN_AREA, OsmtNode.COLUMN_ICON, OsmtNode.COLUMN_WIKI,
				OsmtNode.COLUMN_USE, OsmtNode.COLUMN_IS_BUNDLE,
				OsmtNodeLangText.COLUMN_DISPLAY_NAME, OsmtNodeLangText.COLUMN_COMMENT);
		final String fromClause = String.format(
				" FROM %s LEFT OUTER JOIN %s ON %s=%s AND %s='%s' ", OsmtNode.TABLE,
				OsmtNodeLangText.TABLE, OsmtNode.COLUMN_NODE_ID,
				OsmtNodeLangText.COLUMN_NODE_ID, OsmtNodeLangText.COLUMN_LANGUAGE, appLanguage);
		String whereClause = String.format(" WHERE %s = '%d'", OsmtNode.COLUMN_THEME_ID,
				Integer.valueOf(parentId));

		final boolean filterPois = OsmtElementRecord.isNode(filterElements);
		final boolean filterWays = OsmtElementRecord.isWay(filterElements);
		final boolean filterAreas = OsmtElementRecord.isArea(filterElements);

		if (filterPois) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_POI,
					Integer.valueOf(1));
		}
		if (filterWays) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_WAY,
					Integer.valueOf(1));
		}
		if (filterAreas) {
			whereClause += String.format(" AND %s = '%d' ", OsmtNode.COLUMN_AREA,
					Integer.valueOf(1));
		}
		final String orderClause = String.format("ORDER BY %s DESC", OsmtNode.COLUMN_USE);
		final String query = selectClause + fromClause + whereClause + orderClause;
		database.query(query, new OnObtainCursorListener() {

			@Override
			public boolean onObtainCursor(Cursor cursor) {
				OsmtNodeRecord resultNode;
				if (cursor.moveToFirst()) {
					final int columnNodeIdPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_NODE_ID);
					final int columnThemeIdPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_THEME_ID);
					final int columnPoiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_POI);
					final int columnWayPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_WAY);
					final int columnAreaPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_AREA);
					final int columnIconPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_ICON);
					final int columnWikiPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_WIKI);
					final int columnUsePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_USE);
					final int columnIsBundlePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor, OsmtNode.COLUMN_IS_BUNDLE);
					final int columnDisplayNamePosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtNodeLangText.COLUMN_DISPLAY_NAME);
					final int columnCommentPosition = OsmtDatabaseUtils
							.detectColumnIndexOfCursorByName(cursor,
									OsmtNodeLangText.COLUMN_COMMENT);
					do {
						resultNode = new OsmtNodeRecord();
						resultNode.id = cursor.getInt(columnNodeIdPosition);
						resultNode.themeId = cursor.getInt(columnThemeIdPosition);
						resultNode.elements = OsmtElementRecord.convertElementsToInt(
								cursor.getInt(columnPoiPosition) == 1,
								cursor.getInt(columnWayPosition) == 1,
								cursor.getInt(columnAreaPosition) == 1);
						resultNode.icon = cursor.getString(columnIconPosition);
						resultNode.wiki = cursor.getString(columnWikiPosition);
						resultNode.use = cursor.getInt(columnUsePosition);
						resultNode.isBundle = cursor.getInt(columnIsBundlePosition) == 1;
						resultNode.displayName = cursor.getString(columnDisplayNamePosition);
						resultNode.comment = cursor.getString(columnCommentPosition);
						results.put(Integer.valueOf(resultNode.id), resultNode);
					} while (cursor.moveToNext());
					return true;
				}
				return false;
			}
		});
		return results;
	}
}
